Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Are you ready for more? Today we're going to dive in and start coding our course project. We'll be looking at the code required to create the administration portion of the Food Store application. We need an area where our store managers can enter new products, modify existing products, and process customer orders. These functions often happen in a separate application from the normal storefront Web pages (you wouldn't want your customers trying to get into the store databases).

In this lesson, we'll go over the general layout of the administration Web pages, and I'll share a few tricks for handling the different features contained in them. You'll learn to write Web pages that only registered users can access. You'll also discover how to use PHP to create HTTP headers to redirect a visitor to a specific Web page. Finally, we'll discuss how to incorporate data contained in standard text files into your dynamic Web pages. This will give you yet another avenue for providing dynamic content.

That should be enough to keep us going for today. Let's get started.

Building the Back End

Before we can start working on our storefront Web pages, we need to work on the back-end applications. This is the common term for Web programs that control the behind-the-scenes operations involved in providing data for the main Web application.

Your customers can't start ordering products unless you do some work entering and arranging new products in your database. All of these things happen in the back-end application.

We'll work on five elements to the back-end application in this course:

• Password protection—ensures that only authorized managers can access the back-end application.
• Category management—allows a manager to create the layout for the store products.
• Product management—provides an interface so the store manager can easily create and modify product information.
• Order processing—lets the store manager view and process customer orders.
• Generating reports—generates management reports showing the store activity.

We'll only focus on the first three elements of the back-end application in this lesson. We'll save processing customer orders and generating reports for a later lesson after we have some customer orders to process.

The Back-End Layout

Since my goal for this course is to teach you new and exciting PHP and MySQL things, we won't spend a lot of time burying ourselves in HTML and CSS code to create the course project. We'll obviously need to use some to make our site functional, but it won't be quite as exotic as some online retail stores you've seen. Once you have the basic store template, you'll be able to spruce it up to your own liking after you finish the course.

The core of our back-end application is the admin.php program. It creates a standard Web page format for the application by using an HTML table to divide the Web page into five sections that remain throughout the application.

• A header area that contains a standard banner for each Web page.
• A footer area that contains contact information that appears on each Web page.
• A navigation area that contains links to quickly traverse the Web site.
• A status area that provides real-time information about the store at a glance.
• The main area where individual Web page content appears.

If you took the Introduction to PHP and MySQL course with me, you'll recognize most of this code. If you didn't take the first course, or you need a refresher on what we did in that course, don't worry. In the Supplementary Materials section, you'll find a link to a complete explanation of what each code sections does, along with an explanation of how to use a CSS style sheet in the application.

The application code uses the PHP include() function to include PHP code files to provide unique content for each section. Here's how to create the admin.php file:

1. Create a folder called store under the www folder in the c:\wamp folder.
2. Create another folder called admin under the newly created store folder.
3. Create a text file called admin.php in the admin folder, and enter the following code:

Print code

<?php
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css" />
<title>The Food Store - Admin Console</title>
</head>
<?php
   include("/mylibrary/login.php");
   login();
?>
<body>
<table width="100%" border="0">
  <tr>
    <td id="header" height="90" colspan="3">
<?php include("header.inc.php"); ?></td>
  </tr>
  <tr>
    <td id="nav" width="20%" valign="top">
<?php include("adminnav.inc.php"); ?></td>
    <td id="main" width="50%" valign="top">
  <?php
               if (!isset($_REQUEST['content']))
               {
                   if (!isset($_SESSION['store_admin']))
                      include("adminlogin.html");
                   else
                      include("adminmain.inc.php");
               }
               else
               {
                   $content = $_REQUEST['content'];
                   $nextpage = $content . ".inc.php";
                   include($nextpage);
               } ?></td>
    <td id="status" width="30%" valign="top">
  <?php include("adminstatus.inc.php"); ?></td>
  </tr>
  
  <tr>
    <td id="footer" colspan="3">
  <div align="center">
  <?php include("footer.inc.php"); ?>
  </div></td>
  </tr>
</table>
</body>
</html>

4. Save the file (remember to use double quotes around the filename if you're using Microsoft Notepad).

The admin.php code starts by using the PHP session_start() function. This function ensures that a session cookie will be started for each client session. This is important, as that's how we identify the administrator after he or she logs in.

After the standard HTML stuff, there's an interesting PHP section:

include("/mylibrary/login.php");
login();

The code uses another include() function to incorporate PHP code from another file located in a folder called mylibary. We'll be writing a few functions for our application, and this is the first one. Each function will be stored in the folder mylibary, under the store folder.

Following that, we call a function—login(). This isn't a standard PHP function. It's one that we create in the login.php code. This function will handle the work of logging into the MySQL server and connecting to a default database for us.

Since all of the application code is contained within the admin.php file (via the include() statements), we only need to log into the MySQL database server once. We can then use that one connection throughout the application.

Hiding Connect Information

One trick that's becoming more popular in MySQL programming is using the include() function to hide files that contain sensitive information. The most sensitive information you have in your application is the login information to your MySQL database server, contained in the mysql_connect() function. If hackers got a hold of this file, they'd know the user name and password for your MySQL database. That could be potentially bad.

To prevent that problem, we'll create a separate PHP function called login() and save it in an alternative location. To keep things simple, I'm just including it in the mylibrary folder under our store folder. In real practice, it would be best to place it in a folder out of the Apache Web server's path so it's not accessible via the Web.

Here's how to create the login() function:

1. Create a folder called mylibrary under the store folder.
2. Use Notepad to create a new file called login.php.
3. In the new file, enter the following code:

Print code

<?php
function login()
{
   $con = mysql_connect("localhost", "test", "test") or die('Could not connect to server');
   mysql_select_db("store", $con) or die('Could not connect to database');
}
?>

4. Save the file, and exit Notepad.

The login() function uses the standard mysql_connect() and mysql_select_db() functions to connect to our store database. When you use the login() function in the admin.php file, it establishes a connection to the MySQL database server. This connection stays active throughout the life of the Web page. This feature is called persistent. You can process multiple mysql_query() functions on a single mysql_connect() connection. Since the code defines the login() function at the top of the admin.php file, all SQL queries for any file included in the application file will use this database connection.

This is a great performance boost! Instead of making multiple database connections throughout the application page, it'll just use a single connection. When the Web page finishes loading, PHP closes the connection so it isn't valid across Web pages (it isn't persistent between Web pages).

That's enough for this chapter. In Chapter 3, we'll look at the login Web pages required to allow our store managers to log into the back-end application.

Controlling Logins

Possibly the most important feature of the back-end application is security control. It's imperative that you're able to control who can log in and modify data in your store. It's also a good idea not to provide too much information before a visitor actually logs into the application. For example, you wouldn't want to show menu options that only logged-in users would see.

The Food Store admin application uses session cookies to track logged-in managers. A session cookie holds data for a Web session that persists between Web pages. After the application validates the manager from the admins database table, it creates a session cookie with the user name for the manager.

We'll use that session cookie to determine what content a visitor will see on the admin Web page. If the session cookie isn't present, we won't produce any data. This is a great technique for controlling information on your Web page.

When our store manager first accesses the admin application, we'll need to present a bare-bones Web page that only has a login form, as shown here.

The Food Store admin login page

Notice that the navigation area (the left side of the page) is almost empty. All of the links to features within the application are hidden when the visitor isn't logged in. Let's build the code for that:

1. Create a file called adminnav.inc.php in the admin folder.
2. Enter the following code in the file:

Print code

<table width="100%" cellpadding="2">
  <tr>
    <td><h3>Store Administration</h3></td>
  </tr>
  <tr>
    <td><a href="admin.php"><strong>Home</strong></a></td>
  </tr>
  <tr>
    <td><hr size="1" noshade="noshade" /></td>
  </tr>
<?php
   if (isset($_SESSION['store_admin']))
   {
      echo "<tr><td>\n";
      echo "<form action=\"admin.php\" method=\"get\">\n";
      echo "<label><font color=\"#663300\"><br>Browse Products<br></font> </label>\n";
      echo "<select name=\"cat\">\n";
      $query="SELECT catid,name from categories";
      $result=mysql_query($query);
      while($row=mysql_fetch_array($result,MYSQL_ASSOC))
      {
          $catid = $row['catid'];
          $name = $row['name'];
          echo "<option value=\"$catid\">$name</option>";
      }
      echo "</select>\n";
      echo "<input name=\"goButton\" type=\"submit\" value=\"browse\" />\n";
      echo "<input name=\"content\" type=\"hidden\" value=\"editproducts\" />\n";
      echo "</form> </td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td bgcolor=\"#FFFF99\"><a href=\"admin.php?content=newproduct\"><strong>Add a new product</strong></a></td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td><a href=\"admin.php?content=newcat\"><strong>Add a new category</strong></a></td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td><a href=\"admin.php?content=process\"><strong>Process Pending Orders</strong></a></td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td><a href=\"admin.php?content=outofstock\"><strong>List out-of-stock</strong></a></td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td><a href=\"admin.php?content=report\"><strong>Generate report</strong></a></td></tr>\n";
      echo "<tr><td><hr size=\"1\" noshade=\"noshade\" /></td></tr>\n";
      echo "<tr><td><a href=\"logout.php\"><strong>Log Out</strong></a></td></tr>\n";
      echo "<tr><td> </td></tr>\n";
   }
?>
</table>

3. Save the file, and exit Notepad.

The adminnav.inc.php code creates a simple one-column HTML table in the navigation section of the Web page. In the top row, it displays a title and then a link to the main administration page.

The meat of the adminnav.inc.php page is in the PHP code. The PHP code uses an if-then statement to check if the store_admin session cookie is set. We'll use this session cookie to track our manager.

If the cookie isn't set, none of the navigation menu items will appear. In that case, we'll just exit the if-then statement and close out the table. If the session cookie is set, we know the manager is logged in, and we show the links for the individual sections of the administration application (which we'll be discussing later on). How cool is that?

Providing a Login Form

The next step is to provide a simple form that allows the manager to log in. If you take a look back at the admin.php code you created in Chapter 2, you'll notice that the main section of the Web page uses some PHP code to determine the content it presents:

<?php
   if (!isset($_REQUEST['content']))
   {
      if (!isset($_SESSION['store_admin']))
         include("adminlogin.html");
      else
         include("adminmain.inc.php");
   }
   else
   {
       $content = $_REQUEST['content'];
       $nextpage = $content . ".inc.php";
        include($nextpage);
   } ?>

The application uses an HTML content variable to determine the information to display in the main section of the Web page. This trick allows us to use the same overall Web page format and easily modify the central content just by calling a different include file. If the content variable isn't set (such as when you first access the Web page), the code performs another check. It checks to see if the visitor has the store_admin session cookie set in their browser session.

If the cookie is set, it means the visitor is already logged into the application, and he or she is sent to the next Web page defined in the content HTML variable. If the cookie isn't set, the visitor isn't logged in. So the main section includes the file adminlogin.html.

The adminlogin.html file is a simple HTML file that produces our login form. Let's create that file.

1. Create a file called adminlogin.html in the admin folder (note that it is a .html file and not a .php file.
2. Enter the following code:

<h2><br>Store Web Site Administration<br></h2>
<p>Please log in below</p>
<form action="validate.php" method="post">
<b>User Name:</b><br>
<input type="text" size="20" name="userid"><br>
<br>
<b>Password:</b><br><input type="password" size="20" name="password">
<br><br>
<input type="submit" value="Login">
</form>

This is a simple HTML form asking for the user name and password for our administrator. After the manager clicks the Login button, the page passes the form information to the validate.php file using the HTML POST method. All the action happens here.

The validate.php file isn't really part of our admin Web page. You'll notice that the login form doesn't return us to the admin.php program to display content in the main Web page. Instead, it passes control directly to the validate.php page. The code in the validate.php file checks the login information provided by the manager. Then it forwards the session to the admin.php page, but it also specifies the name of the next Web page to display.

Let's create the validate.php file:

1. Create a file called validate.php in the admin folder.
2. Enter the following code:


Print code

<?php
session_start();
include ("/mylibrary/login.php");
login();
$userid = $_POST['userid'];
$password = $_POST['password'];
$query = "SELECT userid, name from admins where userid = '$userid' and password = PASSWORD('$password')";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0)
{
   echo "<h2>Sorry, your account was not validated.</h2><br>\n";
   echo "<a href=\"admin.php\">Try again</a><br>\n";
} else
{
   $_SESSION['store_admin'] = $userid;
   header("Location: admin.php");
}
?>

3. Save the file and exit.

The validate.php file retrieves the data from the HTML form using the standard PHP $_POST[] array variable and stores the values in PHP variables. Next, it uses those values to create an SQL query to compare the user name and password the manager supplied to the values in the admins database table. If the values don't match an existing record, the code displays a simple error message, along with a link back to the login page.

The cool part happens when the user data is validated.

$_SESSION['store_admin'] = $userid;
header("Location: admin.php");

First, the code creates a store_admin session cookie (remember, that's the session cookie name the code in the adminnav.inc.php file is looking for) and stores the user name there. The next line does some amazing trickery.

The PHP header() function allows us to send HTTP header information to the client browser. HTTP headers control the operation of how the client's browser interprets data in the session. The trick is this function must appear before any HTML code is sent to the client's browser. You can't have any echo statements before the header() function, or it'll fail.

HTTP headers provide for lots of control over the Web session. The HTTP Location header redirects the client browser to another page. In this particular case, we want to return the logged-in manager back to the admin.php page so he or she can now see the full administration Web page.

Now that your store manager is logged into the application, it's time to provide some useful information. We'll take a look at this in Chapter 4.

Displaying Administration Information

Are you still with me? Great! Let's get back to it. The manager has just entered in a username and password, the application has verified that information from the admins database table, then passed the manager back to the admin.php file. Remember, the admin.php file creates the main part of the Web page based on PHP code:

if (!isset($_SESSION['store_admin']))
         include("adminlogin.html");
      else
         include("adminmain.inc.php");

Since the session cookie is set, the manager will see the full navigation menu in the left side of the Web page, and the main section will use the contents of the adminmain.inc.php file. Let's create that file and walk through what it does.

1. Create a new file called adminmain.inc.php in the admin folder.
2. Enter the following code in the file:

Print code

<?php
   $userid = $_SESSION['store_admin'];
   $query = "SELECT name from admins WHERE userid = '$userid'";
   $result=mysql_query($query);
   $row=mysql_fetch_array($result, MYSQL_ASSOC);
   $name = $row['name'];
   echo "<h2>Welcome, $name</h2><br>\n";
   $date = date("l, F j, Y");
   echo "<h2>Today's date: $date</h2><br>\n";
   echo "<h2>Admin messages:</h2>\n";
   if (is_readable("/mylibrary/dailymessages.txt"))
   {
      $message = file_get_contents("/mylibrary/dailymessages.txt");
      $message = nl2br($message);
      echo $message;
   }
   else
   {
      echo "No messages for today.\n";
   }
   echo "<h2><br>Products currently on sale:</h2>\n";
   $query = "SELECT prodid,description,price,quantity from products where onsale = 1";
   $result = mysql_query($query);
   while($row=mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $prodid = $row['prodid'];
      $description = $row['description'];
      $price = $row['price'];
      $quantity = $row['quantity'];
      printf("<a href=\"admin.php?content=updateproduct&id=$prodid\">%s</a>   - $%.2lf\n", $description, $price);
      if ($quantity == 0)
         echo "  <font color=\"ff0000\">OUT OF STOCK</font><br>\n";
      else
         echo "<br>";
   }
?>

The first part of the adminmain.inc.php should look familiar to you. It extracts the manager's username from the session cookie, performs an SQL query to find the name value in the database table, displays a personalized welcome message, and shows the current date using the PHP date() function. It uses a few exotic parameters for the date() function that show the full text day, the full text month, then the numeric day and the numeric year.

The last section should also look familiar. It sends an SQL query to the MySQL database looking for products that have the onsale data field set. It then displays basic information, showing which products are on sale, their current price, and if they're out of stock. One thing to note here is the way the code displays the information:

Print code


printf("<a href=\"admin.php?content=updateproduct&id=$prodid\">%s</a>   - $%.2lf\n", $description, $price);

The printf() function works similar to the echo statement, but it allows us to format data when displaying it. We'll look more closely at this function in a later lesson.

The middle part of the code is something you may not be familiar with from your PHP coding experiences. It reads data from a text file and displays it on the Web page.

Reading Data From Text Files

The middle section of the adminmain.inc.php file uses a feature of PHP that allows us to retrieve data from simple text files. This is a great tool for posting quick information without messing with the database or importing data from another application into your application. In our case, we'll allow a store senior manager (or possibly the owner) to create a simple text file to hold messages the managers will see when they log into the administration application.



The main section of the Web page

The PHP application can access and process any file it has privileges to on the server. The file access is controlled by the user account restrictions that started the Apache Web server. On a Windows server, that shouldn't be much of a problem, as the Apache Web server often starts as an administrator service. You should have access to any file on the Windows server.

On a Unix, Linux, or Macintosh server, this may get more tricky. Usually these servers run the Apache Web server from a separate user ID (usually called apache). To be able to read data from a file, the apache user ID must have privileges to read the file.

On our WampServer, all should be well, so we can play with this feature. Here's the code from the adminmain.inc.php file that we're using to process the text file:

Print code

if (is_readable("/mylibrary/dailymessages.txt"))
   {
      $message = file_get_contents("/mylibrary/dailymessages.txt");
      $message = nl2br($message);
      echo $message;
}
   else
   {
      echo "No messages for today.\n";
   }

First, the code uses the is_readable() PHP function. This function checks to see if the file exists on the system. It also makes sure that the user ID the Web server is running under has read access to it. For our test, we'll just put a text file called dailymessages.txt in the mylibrary folder. If this test fails, the code just prints out a message indicating that there aren't any messages.

If the test passes, the code uses the file_get_contents() PHP function to read the entire file into a single PHP variable. This is a great feature, because once the variable contains the file contents, you can step through the file just by processing the variable. This example uses the nl2br() PHP function to convert newline characters to HTML tags in the string. This makes the message appear on the Web page just as it appears in the text file.

Creating a Status Section

Another feature you'll find in many modern Web applications is a single status area, often called a dashboard. Much like the dashboard on a car, a Web application dashboard provides a single place to display important information about the application.



The Food Store dashboard

The dashboard in our administration application provides the following information:

• The number of products currently available in the store.
• The quantity of products that are out of stock.
• The amount of customer orders waiting to be processed.

Obviously you can provide as much or as little information in your dashboard area as you want. The idea is to provide crucial information to your manager immediately without making him or her search through the data manually.

The dashboard information is a result of multiple SQL queries. Often you can provide a quick SQL query to produce information that would take the manager extra time to calculate. For example, we can easily find the number of products that are out of stock with the simple query:

SELECT prodid FROM products WHERE quantity = 0;

Without this simple query, the manager would have to look through all of the individual products to find the ones that show a zero quantity value.

Let's create the dashboard file for our administration application. The admin.php file includes the file adminstatus.inc.php in the status section, so that's what we'll build:

1. Create the file adminstatus.inc.php in the admin folder.
2. Enter the following code:

Print code

<?php
   if (isset($_SESSION['store_admin']))
   {
      echo "<h2>Current store status:</h2>\n";
      echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
      $query = "SELECT prodid from products";
      $result = mysql_query($query);
      $totprods = mysql_num_rows($result);
      echo "<tr><td>Products in store</td><td>$totprods</td></tr>\n";
      $query="SELECT prodid from products where quantity = 0";
      $result = mysql_query($query);
      $totout = mysql_num_rows($result);
      echo "<tr><td>Products out of stock</td><td>$totout</td></tr>\n";
      $query = "SELECT orderid from orders where status = 'pending'";
      $result = mysql_query($query);
      $totpending = mysql_num_rows($result);
      echo "<tr><td>Orders Pending</td><td>$totpending</td</tr>\n";
      echo "</table>\n";
   }
?>

3. Save the file and exit.

Notice that the dashboard is hidden from sight unless the visitor has the store_admin session cookie set.

The power of the dashboard is in the SQL queries, not necessarily the PHP or HTML code. You'll see that the code presents the dashboard as a simple HTML table. It provides the basic information in an easy-to-read format. The SQL queries do all of the hard work. The PHP mysql_num_rows() function counts the number of records returned in the result set without us having to iterate through all of the records!

Note: Be careful with how complex you make the SQL queries in your dashboard. You don't want to use a query that takes a long time to complete, as it will slow down your entire Web page.

That should be enough for today. Let's move on to Chapter 5 and recap what we've learned.

Summary

Today we started building the core of the Food Store course project. An important part of any Web application is the ability to enter new data. Many applications use separate Web pages called back-end applications to separate out the data-entry part from the normal data-viewing part.

The Food Store uses a simple HTML table and PHP include() functions to present a common Web page appearance throughout the application. The first thing a visitor sees is a login form that hides items until the visitor successfully logs into the application. The store_admin session cookie controls if the visitor is an authenticated user or not.

Once a manager is logged into the administration application, he or she will see lots of useful information. The main Web page area uses the PHP file-handling functions to read a message text file and display its contents.

Finally, we discussed another popular feature of Web pages called a dashboard. The dashboard provides a single area for viewing crucial application information.

In the next lesson, we'll cover some more of the administration features of the application. You'll discover how the store manager can post new product information (including pictures) to the database.

Don't forget to complete today's assignment, as we'll be creating some user accounts in the database and application that make this whole thing work.